from jobs.sql_mehods import   *
import pandas as pd
db1 = MySQLHelper(host='mmservice-05.mysql.hotgrid.cn', port=3306,
                 user='electricity_api_service', password='GJlfh7&#jg',
                 db='electricity_data', charset='utf8')
db2 = MySQLHelper(host='mmservice-05.mysql.hotgrid.cn', port=3306,
                 user='electricity_api_service', password='GJlfh7&#jg',
                 db='electricity_data', charset='utf8')


# 查询结束时间在1个小时之内
from datetime import datetime,timedelta
last_hour_time=(datetime.now()-timedelta(hours=1.5)).strftime("%Y-%m-%d %H:%M:%S")
last_hour_time2=(datetime.now()-timedelta(hours=2.5)).strftime("%Y-%m-%d %H:%M:%S")
print(last_hour_time)
sql=f"""select * from elec_warn_records 
where end_time >='{last_hour_time}'
and rule_number =2
GROUP BY ent_name,dev_name"""

last_hour_data = db1.execute_charts(sql)



dev_id_list = "'"+"','".join(map(str,last_hour_data['dev_id']))+"'"
#
print(dev_id_list)

# 查看这些企业是否近40分钟内五分钟是否还存在 违规
# 从dev_id中 拿到 他们的guid
# 然后接着那他们最近1h的5m数据
guid_sql = f"""select * from elecdata_basic_info where devid in ({dev_id_list}) and data_type_id=8"""

guid_list=db1.execute_charts(guid_sql)

print(guid_list['guid'])
guid_list = "'"+"','".join(guid_list['guid'])+"'"
# 查询存在这些的最近一小时的五分钟数据  最近一小时变量是 last_hour_time

print(guid_list)
guid_warn_last_hour_sql=f"""select * from elecdata_data_5m_202401 where data_time >='{last_hour_time}'
and guid in ({guid_list})"""

guid_warn_last_hour_object=db1.execute_select(guid_warn_last_hour_sql)

guid_warn_last_hour_data = pd.DataFrame(guid_warn_last_hour_object)

print(guid_warn_last_hour_data)
# 确保值是浮点数
guid_warn_last_hour_data['value'] = guid_warn_last_hour_data['value'].astype(float)

# 对'guid'字段进行分组，在每个分组内部对'value'字段按照'data_time'进行排序
guid_warn_last_hour_data.sort_values('data_time', ascending=False, inplace=True)  # 时间倒序

# 在每个分组内部计算'value'字段的残差
guid_warn_last_hour_data['residual'] = guid_warn_last_hour_data.groupby('guid')['value'].diff()

# 打印结果
print(guid_warn_last_hour_data)


# 计算每个guid的非零残差数量
non_zero_residual_counts = guid_warn_last_hour_data[guid_warn_last_hour_data['residual'] != 0].groupby('guid')['residual'].count()

# 获取active_guid_list和unactive_guid_list
active_guid_list = non_zero_residual_counts[non_zero_residual_counts > 2].index.tolist()
unactive_guid_list = non_zero_residual_counts[non_zero_residual_counts <= 2].index.tolist()
active_guid_list = "'"+"','".join(active_guid_list)+"'"
unactive_guid_list = "'"+"','".join(unactive_guid_list)+"'"
# 打印结果
print("活跃的 GUIDs: ", active_guid_list)
print("不活跃的 GUIDs: ", unactive_guid_list)

# 查询这些活跃的guid 和不活跃的guid对应的devid
active_devid_sql=f"""select devid from elecdata_basic_info where guid in ({active_guid_list})"""
unactive_devid_sql=f"""select devid from elecdata_basic_info where guid in ({unactive_guid_list})"""
active_devid="'"+"','".join(db1.execute_charts(active_devid_sql)['devid'])+"'"
unactive_devid="'"+"','".join(db1.execute_charts(unactive_devid_sql)['devid'])+"'"
print(active_devid)
print(unactive_devid)

# 查询未释放的目前报警情况
# 对报警的不活跃的guid进行一次释放处理
unrelsedsql=f"""UPDATE elec_warn_records 
SET end_time = DATE_SUB(NOW(), INTERVAL 5 MINUTE),
    duration = CONCAT(
       CASE
         WHEN FLOOR(TIMESTAMPDIFF(MINUTE , start_time, DATE_SUB(NOW(), INTERVAL 5 MINUTE)) / 1440) > 0 THEN
           CONCAT(FLOOR(TIMESTAMPDIFF(MINUTE , start_time, DATE_SUB(NOW(), INTERVAL 5 MINUTE)) / 1440), '天')
         ELSE ''
       END,
       CASE
         WHEN FLOOR((TIMESTAMPDIFF(MINUTE , start_time, DATE_SUB(NOW(), INTERVAL 5 MINUTE)) % 1440) / 60) > 0 THEN
           CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE , start_time, DATE_SUB(NOW(), INTERVAL 5 MINUTE)) % 1440) / 60), '小时')
         ELSE ''
       END,
       MOD(TIMESTAMPDIFF(MINUTE , start_time, DATE_SUB(NOW(), INTERVAL 5 MINUTE)), 60), '分钟'),
    release_status = 0
WHERE dev_id in  ({unactive_devid})
and end_time >='{last_hour_time}' and rule_number =2 
and start_time >='{last_hour_time2}';"""

print(unrelsedsql)

result=db1.execute_edit(unrelsedsql)
print(result)


guid_warn_last_hour_data.to_csv("最近一小时报警数据.csv")







